-- @owner: @haomeng802
-- @date: 2023/1/3
-- @testpoint: 临时表结合connect by子句
-- @modify: modified by @kyeleze at 2023/05/24 代码变更，优化用例期望

--step1:创建全局临时表，插入数据  expect:建表成功，插入数据成功
drop table if exists t_connectby_003_1 cascade;
create global temporary table t_connectby_003_1(e_id int,l_id int,e_name varchar(10),d_num varchar(5));

insert into t_connectby_003_1 values(3,1,'arise','003');
insert into t_connectby_003_1 values(2,1,'jack','002');
insert into t_connectby_003_1 values(5,2,'judy','002');
insert into t_connectby_003_1 values(6,3,'lion','003');
insert into t_connectby_003_1 values(4,2,'scott','002');
insert into t_connectby_003_1 values(7,3,'tiger','003');
insert into t_connectby_003_1 values(1,0,'king','001');

--step2:查询以l_id为0开始的节点的所有直属节点  expect:成功
select * from t_connectby_003_1 start with l_id = 0 connect by prior e_id = l_id;

--step3:结合层次递归查询函数查看递归层级  expect:成功
select sys_connect_by_path(e_name,'-') as path, *, level from t_connectby_003_1 start with l_id = 0 connect by prior e_id = l_id;

--step4:创建本地临时表，插入数据  expect:建表成功，插入数据成功
drop table if exists t_connectby_003_2 cascade;
create temporary table t_connectby_003_2(e_id int,l_id int,e_name varchar(10),d_num varchar(5));

insert into t_connectby_003_2 values(3,1,'arise','003');
insert into t_connectby_003_2 values(2,1,'jack','002');
insert into t_connectby_003_2 values(5,2,'judy','002');
insert into t_connectby_003_2 values(6,3,'lion','003');
insert into t_connectby_003_2 values(4,2,'scott','002');
insert into t_connectby_003_2 values(7,3,'tiger','003');
insert into t_connectby_003_2 values(1,0,'king','001');

--step5:查询以l_id为0开始的节点的所有直属节点  expect:成功
select * from t_connectby_003_2 start with l_id = 0 connect by prior l_id = e_id;

--step6:结合层次递归查询函数查看递归层级  expect:成功
select sys_connect_by_path(e_name,'-') as path, *, level from t_connectby_003_2 start with l_id = 0 connect by prior e_id = l_id;

--step7:清理环境   expect:成功
drop table t_connectby_003_1 cascade;
drop table t_connectby_003_2 cascade;
